/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package creadb;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.io.File;
import java.io.FilenameFilter;
import java.sql.DriverManager;
import java.sql.Timestamp;
import java.util.Random;

/**
 *
 * @author User
 */
public class CreatoreDB {
    public CreatoreDB() {
        Connection c=connection("localhost", 3306, "root", "");
        
        File folder=new File("D:/Documenti/Scuola/III anno/Programmazione per il Web/Progetti/Progetto1/progetto SVN/Materiale/CreaDB/db/foto");
        System.out.println("Path: "+folder.exists()+folder.getPath());
        String[] lista=folder.list(new FilenameFilter() {
            @Override
            public boolean accept(File dir, String name) {
                return name.endsWith(".jpg");
            }
        });
        Random r=new Random(System.currentTimeMillis()/101);    //Generatore di numeri casuali
        
        //Creazione DB
        executeStatement(c, "CREATE DATABASE IF NOT EXISTS Vaccinazioni");
        c=connection("localhost", 3306, "Vaccinazioni", "root", "");
        
        //Creazione tabelle
        executeStatement(c, "CREATE TABLE Medici (id_medico INT AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(25), cognome VARCHAR(25), password VARCHAR(25))");
        executeStatement(c, "CREATE TABLE Pazienti (id_paziente INT AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(25), cognome VARCHAR(25), data_di_nascita DATE, foto VARCHAR(255), password VARCHAR(25))");
        executeStatement(c, "CREATE TABLE Richiami (id_richiamo INT AUTO_INCREMENT PRIMARY KEY, paziente INT, medico INT, data DATETIME)");
        
        //Inserimento valori in tabella Medici
        executeStatement(c, "INSERT INTO Medici VALUES (NULL, \"Gregory\", \"House\", \"password1\")");
        executeStatement(c, "INSERT INTO Medici VALUES (NULL, \"Hannibal\", \"Lecter\", \"password2\")");
        executeStatement(c, "INSERT INTO Medici VALUES (NULL, \"Josef\", \"Mengele\", \"password3\")");
    
        //Inserimento valori in tabella Pazienti
        for(int i=0; i<lista.length; i++) {
            String stringa=lista[i];
            String nome=stringa.substring(0, stringa.indexOf(' '));
            String cognome=stringa.substring(stringa.indexOf(' ')+1, stringa.length()-4);
            String data_di_nascita=(1930+r.nextInt(35))+"/"+(1+r.nextInt(12))+"/"+(1+r.nextInt(28));
            String foto="img/foto/"+stringa;
            //String password="psw"+i;                     //Da generare casualmente
            executeStatement(c, "INSERT INTO Pazienti VALUES (NULL, \""+nome+"\", \""+cognome+"\", \""+data_di_nascita+"\", \""+foto+"\", NULL)");
        }
        
        //Inserimento valori in tabella Vaccinazioni
        for(int i=0; i<(lista.length*2); i++) {
            int paziente=1+r.nextInt(lista.length);
            int medico=1+r.nextInt(3);
            String data=(2000+r.nextInt(12))+"/"+(1+r.nextInt(12))+"/"+(1+r.nextInt(28)+" "+(8+r.nextInt(9))+":"+r.nextInt(60)+";"+r.nextInt(60));
            executeStatement(c, "INSERT INTO Richiami VALUES (NULL, "+paziente+", "+medico+", \""+data+"\")");
        }
    }
    
    public static Connection connection(String host, int port, String userName, String password) {
        Connection dbConection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String dbString="jdbc:mysql://"+host+"/";
            dbConection=(Connection)DriverManager.getConnection(dbString, userName, password);
            System.out.println("Connected with the DB");
        } catch (Exception e) {
            System.err.println("Failed to connect with the DB");
            e.printStackTrace();
        }
        return dbConection;        
    }
    
    public static Connection connection(String host, int port, String dbName, String userName, String password) {
        Connection dbConection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String dbString="jdbc:mysql://"+host+"/"+dbName;
            dbConection=(Connection)DriverManager.getConnection(dbString, userName, password);
            System.out.println("Connected with the DB "+dbName);
        } catch (Exception e) {
            System.err.println("Failed to connect with the DB");
            e.printStackTrace();
        }
        return dbConection;        
    }
    
    public static void executeStatement(Connection c, String statement) {
        try {
            System.out.println(statement);
            Statement s=(Statement)c.createStatement();
            s.execute(statement);
            s.close();
            System.out.println("Statement executed correctly");
        } catch (Exception ex) {
            System.err.println("Error during the execution of the statement");
            ex.printStackTrace();
        }
    }
}
